from flask import Blueprint, request, g, session, jsonify
from Logger import logger
from datetime import datetime
from utils import MyResponse, handle_exception, handle_success, to_lower_camel_case
from db_handler import db_handler

taskStatistics = Blueprint('taskStatistics', __name__)


@taskStatistics.route('/getProjectStatistics', methods=['POST'])
def get_project_statistics():
    try:
        # 获取请求参数
        params = request.json
        creater = params.get("creater", "")
        project_name = params.get("projectName", "")
        pageNum = int(params.get("pageNum", 1))  # 默认第一页
        pageSize = int(params.get("pageSize", 20))  # 默认每页20条

        select_query = """
        SELECT 
            p.id,
            p.project_name,
            tr.slaver_ip,
            tr.status,
            COUNT(tr3.id) AS execution_count,
            (
                SELECT tr4.run_user 
                FROM TaskRun tr4 
                WHERE tr4.run_time = (
                    SELECT MAX(tr5.run_time) 
                    FROM TaskRun tr5 
                    WHERE tr5.project_id = tr.project_id
                ) 
                AND tr4.project_id = tr.project_id
            ) AS latest_run_user
        FROM project p
        LEFT JOIN (
            SELECT tr1.project_id, tr1.slaver_ip, tr1.status 
            FROM TaskRun tr1
            WHERE tr1.run_time = (
                SELECT MAX(tr2.run_time)
                FROM TaskRun tr2 
                WHERE tr2.project_id = tr1.project_id
            )
        ) tr ON p.id = tr.project_id
        LEFT JOIN TaskRun tr3 ON p.id = tr3.project_id 
        WHERE p.project_name LIKE %s AND p.creater LIKE %s 
        GROUP BY p.id, p.project_name, tr.slaver_ip, tr.status
        ORDER BY 
            CASE 
                WHEN tr.status = 'running' THEN 1 
                ELSE 2 
            END,
            execution_count DESC
        LIMIT %s, %s
        """
        start_index = (pageNum - 1) * pageSize
        project_name_like = f"%{project_name}%"
        creater_like = f"%{creater}%"

        results = db_handler.execute_query(select_query, (project_name_like, creater_like, start_index, pageSize))
        print(results)

        # 将字典键从蛇形命名转换为小驼峰命名
        transformed_project_data = [
            {to_lower_camel_case(k): v for k, v in project.items()}
            for project in results
        ]

        # 替换status、slaverIp和latestRunUser为'NULL'如果它们的值为None
        for project in transformed_project_data:
            if project.get('status') is None:
                project['status'] = 'NULL'
            if project.get('slaverIp') is None:
                project['slaverIp'] = 'NULL'
            if project.get('latestRunUser') is None:
                project['latestRunUser'] = 'NULL'

        # 获取总条目数
        count_query = "SELECT COUNT(*) as total FROM project WHERE (creater LIKE %s AND project_name LIKE %s)"
        total_count = db_handler.execute_query(count_query, (creater_like, project_name_like))[0]['total']

        # 构造返回结果
        result = {
            "list": transformed_project_data,
            "total": total_count
        }
        return handle_success(result, "success")
    except Exception as err:
        return handle_exception(err, "程序异常")


@taskStatistics.route('/getProjectDetailInfo', methods=['POST'])
def get_project_detail_info():
    try:
        # 获取请求参数
        params = request.json
        project_id = params.get("projectId", "")
        run_user = params.get("runUser", "")
        run_scenarios = params.get("runScenarios", "")
        pageNum = int(params.get("pageNum", 1))  # 默认第一页
        pageSize = int(params.get("pageSize", 20))  # 默认每页20条

        # 查询语句
        select_query = "SELECT * FROM taskrun WHERE (project_id LIKE %s AND run_user LIKE %s AND run_scenarios LIKE %s) ORDER BY run_time DESC LIMIT %s, %s"


        start_index = (pageNum - 1) * pageSize
        run_user_like = f"%{run_user}%"
        run_scenarios_like = f"%{run_scenarios}%"

        results = db_handler.execute_query(select_query,
                                           (project_id, run_user_like, run_scenarios_like, start_index, pageSize))

        print(results)

        # 格式化 runtime 字段（datetime 对象）
        for project in results:
            if 'run_time' in project and isinstance(project['run_time'], datetime):
                project['run_time'] = project['run_time'].strftime('%Y-%m-%d %H:%M:%S')

        print(results)

        # 将字典键从蛇形命名转换为小驼峰命名
        transformed_project_data = [
            {to_lower_camel_case(k): v for k, v in project.items()}
            for project in results
        ]

        # 获取总条目数
        count_query = "SELECT COUNT(*) as total FROM taskrun WHERE (project_id LIKE %s AND run_user LIKE %s AND run_scenarios LIKE %s)"
        total_count = db_handler.execute_query(count_query, (project_id, run_user_like, run_scenarios_like))[0]['total']

        # 构造返回结果
        result = {
            "list": transformed_project_data,
            "total": total_count
        }
        return handle_success(result, "success")
    except Exception as err:
        return handle_exception(err, "程序异常")
